<?php

namespace app\admin\controller\vip;

use app\common\controller\Backend;
use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
use PhpOffice\PhpSpreadsheet\Reader\Xlsx;
use PhpOffice\PhpSpreadsheet\Reader\Xls;
use PhpOffice\PhpSpreadsheet\Reader\Csv;
use think\Db;

/**
 * 
 *
 * @icon fa fa-circle-o
 */
class Orders extends Backend
{
    
    /**
     * Orders模型对象
     * @var \app\admin\model\vip\Orders
     */
    protected $model = null;
    public $caijinData;

    public function _initialize()
    {
        parent::_initialize();
        $this->model = new \app\admin\model\vip\Orders;
        $this->view->assign("statusList", $this->model->getStatusList());

        $this->caijinData = Db::name("vip_caijin")->order("save_money desc")->cache(true)->select();

    }
    
    /**
     * 默认生成的控制器所继承的父类中有index/add/edit/del/multi五个基础方法、destroy/restore/recyclebin三个回收站方法
     * 因此在当前控制器中可不用编写增删改查的代码,除非需要自己控制这部分逻辑
     * 需要将application/admin/library/traits/Backend.php中对应的方法复制到当前控制器,然后进行修改
     */


    /**
     * 查看
     */
    public function index()
    {
        //设置过滤方法
        $this->request->filter(['strip_tags']);
        if ($this->request->isAjax()) {
            //如果发送的来源是Selectpage，则转发到Selectpage
            if ($this->request->request('keyField')) {
                return $this->selectpage();
            }
            list($where, $sort, $order, $offset, $limit) = $this->buildparams();
            $total = $this->model
                ->where($where)
                ->order($sort, $order)
                ->count();

            $list = $this->model
                ->where($where)
                ->order($sort, $order)
                ->limit($offset, $limit)
                ->select();

            $list = collection($list)->toArray();
            // 查询总金额
            $money = $this->model->where($where)->sum("money");
            $result = array("total" => $total, "rows" => $list,"extend" => ['money' => $money]);

            return json($result);
        }
        return $this->view->fetch();
    }

    /**
     * 导入 直接导入用户流水数据并添加到用户数据表
     */
    public function import()
    {
        // http://vip.cc/admin/vip/orders/import
        set_time_limit(0);
        // 查询VIP等级配置信息
        $levelData = Db::name("vip_description")->order("save_money asc")->cache(true)->select();
        //dump("1111");die;
        $file = $this->request->request('file');
        //$file = "/uploads/20190919/1.xls";
        //$file = "/uploads/20190919/6d05391d8a803ca6ba6e3912452ebe8f.xls";
        //dump($file);
        //die;
        if (!$file) {
            $this->error(__('Parameter %s can not be empty', 'file'));
        }
        $filePath = ROOT_PATH . DS . 'public' . DS . $file;
        if (!is_file($filePath)) {
            $this->error(__('No results were found'));
        }
        //实例化reader
        $ext = pathinfo($filePath, PATHINFO_EXTENSION);
        if (!in_array($ext, ['csv', 'xls', 'xlsx'])) {
            $this->error(__('Unknown data format'));
        }
        if ($ext === 'csv') {
            $file = fopen($filePath, 'r');
            $filePath = tempnam(sys_get_temp_dir(), 'import_csv');
            $fp = fopen($filePath, "w");
            $n = 0;
            while ($line = fgets($file)) {
                $line = rtrim($line, "\n\r\0");
                $encoding = mb_detect_encoding($line, ['utf-8', 'gbk', 'latin1', 'big5']);
                if ($encoding != 'utf-8') {
                    $line = mb_convert_encoding($line, 'utf-8', $encoding);
                }
                if ($n == 0 || preg_match('/^".*"$/', $line)) {
                    fwrite($fp, $line . "\n");
                } else {
                    fwrite($fp, '"' . str_replace(['"', ','], ['""', '","'], $line) . "\"\n");
                }
                $n++;
            }
            fclose($file) || fclose($fp);

            $reader = new Csv();
        } elseif ($ext === 'xls') {
            $reader = new Xls();
        } else {
            $reader = new Xlsx();
        }

        //导入文件首行类型,默认是注释,如果需要使用字段名称请使用name
        $importHeadType = isset($this->importHeadType) ? $this->importHeadType : 'comment';

        $table = $this->model->getQuery()->getTable();
        $database = \think\Config::get('database.database');
        $fieldArr = [];
        $list = db()->query("SELECT COLUMN_NAME,COLUMN_COMMENT FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = ? AND TABLE_SCHEMA = ?", [$table, $database]);
        foreach ($list as $k => $v) {
            if ($importHeadType == 'comment') {
                $fieldArr[$v['COLUMN_COMMENT']] = $v['COLUMN_NAME'];
            } else {
                $fieldArr[$v['COLUMN_NAME']] = $v['COLUMN_NAME'];
            }
        }

        //加载文件
        $insert = [];
        try {
            if (!$PHPExcel = $reader->load($filePath)) {
                $this->errr(__('Unknown data format'));
            }
            $currentSheet = $PHPExcel->getSheet(0);  //读取文件中的第一个工作表
            $allColumn = $currentSheet->getHighestDataColumn(); //取得最大的列号
            $allRow = $currentSheet->getHighestRow(); //取得一共有多少行
            $maxColumnNumber = Coordinate::columnIndexFromString($allColumn);
            $fields = [];
            for ($currentRow = 1; $currentRow <= 1; $currentRow++) {
                for ($currentColumn = 1; $currentColumn <= $maxColumnNumber; $currentColumn++) {
                    $val = $currentSheet->getCellByColumnAndRow($currentColumn, $currentRow)->getValue();
                    $fields[] = $val;
                }
            }

            for ($currentRow = 2; $currentRow <= $allRow; $currentRow++) {
                $values = [];
                for ($currentColumn = 1; $currentColumn <= $maxColumnNumber; $currentColumn++) {
                    $val = $currentSheet->getCellByColumnAndRow($currentColumn, $currentRow)->getValue();
                    $values[] = is_null($val) ? '' : $val;
                }
                $row = [];
                $temp = array_combine($fields, $values);
                if(empty($temp["用户名"])){
                    continue;
                }
                foreach ($temp as $k => $v) {
                    if (isset($fieldArr[$k]) && $k !== '') {
                        $row[$fieldArr[$k]] = $v;
                    }
                }
                //echo "---";
                //dump($row);
                //echo "---";


                // 处理每一个用户数据
                $row["savetime"] = strtotime($row["savetime"]);
                $row["status"] = 1;
                $re = Db::name("vip_orders")->insert($row);
                if(!$re){
                    continue;
                }


                // 录入到用户表
                $userData = Db::name("vip_user")->where("username",$row["username"])->find();
                if(!empty($userData)){
                    $money = $userData["money"] + $row["money"];
                    $level = $this->slevel($money,$levelData);
                    // 查看是否晋级VIP
                    if($level > $userData["level"]){
                        $level_money = Db::name("vip_description")->where("level","gt",$userData["level"])->where("level","elt",$level)->select();//奖金
                        //dump($level_money);die;
                        foreach($level_money as $k=>$v){
                            $jiangli[$k]["username"] = $userData["username"];
                            $jiangli[$k]["money"] = $v["money"];
                            $jiangli[$k]["level"] = $v["level"];
                            $jiangli[$k]["status"] = "0";
                            $jiangli[$k]["createtime"] = time();
                        }
                        if(!empty($jiangli)){
                            Db::name("vip_jiangli")->insertAll($jiangli);
                        }
                    }
                    //
                    Db::name("vip_user")->where("id",$userData["id"])->update(["money"=>$money,"level"=>$level,"updatetime"=>time()]);
                } else {
                    $money = $row["money"];
                    $level = $this->slevel($money,$levelData);
                    Db::name("vip_user")->insert(["username"=>$row["username"],"money"=>$money,"level"=>$level,"createtime"=>time()]);
                    // 查看是否晋级VIP
                    if($level > 0){
                        $level_money = Db::name("vip_description")->where("level","elt",$level)->select();//奖金
                        foreach($level_money as $k=>$v){
                            $jiangli[$k]["username"] = $row["username"];
                            $jiangli[$k]["money"] = $v["money"];
                            $jiangli[$k]["level"] = $v["level"];
                            $jiangli[$k]["status"] = 0;
                            $jiangli[$k]["createtime"] = time();
                        }
                        if(!empty($jiangli)){
                            Db::name("vip_jiangli")->insertAll($jiangli);
                        }
                    }
                    //
                }
                // end


                // 录入彩金月信息
                $this->haoyun($row);


                if ($row) {
                    $insert[] = $row;
                }
            }
        } catch (Exception $exception) {
            $this->error($exception->getMessage());
        }
        if (!$insert) {
            $this->error(__('No rows were updated'));
        }

        //dump("=========");
        //dump($fieldArr);
        //dump("=========");
        //die;

        //dump("全部导入成功");die;
        $this->success();
    }


    /**
     * 归档好运奖金
     * @param $row 用户名流水数据
     * @throws Exception
     */
    public function haoyun($row)
    {
        // 用户名 + 当前存入的金额 + 存入时间
        $username = $row["username"];
        $savemoney = $row["money"];

        $year = date('Y',$row["savetime"]);
        $month = date('m',$row["savetime"]) + 0;
        $day = date('d',$row["savetime"]);
        if($day >= 18) {
            $month = $month + 1;
        }
        $savetime = $year . "-" .$month;

        // 查询用户VIP等级
        $level = Db::name("vip_user")->where("username",$username)->value("level");
        // 查询本月用户是否存在
        $user = Db::name("vip_haoyun")->where("username",$username)->where("savetime",$savetime)->find();
        if(empty($user)){
            $money = $this->s_caijin($savemoney,$level);
            Db::name("vip_haoyun")->insert(["username"=>$username,"savetime"=>$savetime,"savemoney"=>$savemoney,"money"=>$money,"status"=>0]);
        } else {
            $savemoney = $user["savemoney"] + $savemoney;
            $money = $this->s_caijin($savemoney,$level);
            Db::name("vip_haoyun")->where("username",$username)->where("savetime",$savetime)->update(["savemoney"=>$savemoney,"money"=>$money]);
        }
    }

    /**
     * 查询当前彩金对应奖励
     */
    public function s_caijin($save_money,$level)
    {
        $caijinData = $this->caijinData;
        $money = 0;
        foreach($caijinData as $v){
            if($save_money >= $v["save_money"] && $level >= $v["level_start"]){
                $money = $v["money"];
                break;
            }
        }
        return $money;
    }

    /**
     * 判断等级
     */
    public function slevel($num,$levelData)
    {
        $leve = 0;
        foreach($levelData as $v){
            if($num >= $v["save_money"]){
                $leve = $v["level"];
            }
        }
        return $leve;
    }
    

}
